﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;
using System.Windows.Forms;
using System.Data;

namespace Test_ApplicationBD
{
    public class Commandehelper
    {
        private const string MyConString = "SERVER=192.168.1.111;" +
                "DATABASE=applicationCD;" +
                "UID=kecoo;" +
                "PASSWORD=kevin64;";   
        public static List<object[]> RequeteComplexe(string pCommandSQL)
        {
            if (pCommandSQL[pCommandSQL.Length - 1] != ';')
            {
                MessageBox.Show("La rêquete doit se terminer par un ';'", "Erreur", MessageBoxButtons.OK); ; ; ;
                return null;
               
            }

            //pCommandSQL = pCommandSQL.ToLower();


            List<object[]> m_colObject = new List<object[]>();


            /*  Instanciation de la connexion   */
            MySqlConnection connection = new MySqlConnection(MyConString);
            MySqlCommand command = connection.CreateCommand();
            /*  ==Ouverture de la connexion==   */
            connection.Open();
            command.CommandText = pCommandSQL;
            try
            {
                /*      Si Rêquete Select           */
                if (pCommandSQL.ToLower().Contains("select"))
                {

                    MySqlDataReader Reader;
                    Reader = command.ExecuteReader(CommandBehavior.CloseConnection);
                    m_colObject = new List<object[]>();

                    while (Reader.Read())
                    {
                        object[] tabObject = new object[Reader.FieldCount];
                        for (int i = 0; i < Reader.FieldCount; i++)
                        {
                            tabObject[i] = Reader[i];
                        }
                        m_colObject.Add(tabObject);
                    }

                }
                /* Si requete Insert */
                else if (pCommandSQL.ToLower().Contains("insert"))
                {
                    command.ExecuteNonQuery();
                    m_colObject = null;
                }
                else if (pCommandSQL.ToLower().Contains("update"))
                {
                    command.ExecuteNonQuery();
                    m_colObject = null;

                }
                else if (pCommandSQL.ToLower().Contains("delete"))
                {
                    command.ExecuteNonQuery();
                    m_colObject = null;
                }

            }
            catch (Exception)
            {

                MessageBox.Show("Erreur dans la synthaxe");
                return null;
            }




            connection.Close();
            return m_colObject;
        }

        public static object RequeteSimple(string pString)
        {
            MySqlConnection connection = new MySqlConnection(MyConString);
            MySqlCommand command = connection.CreateCommand();
            /*  ==Ouverture de la connexion==   */
            connection.Open();
            command.CommandText = pString;
            object objobject = command.ExecuteScalar();
            connection.Close();
            return objobject;
            
        }

        public static void RemplirLsv(ListView pObjLsv, string pNomTable)
        {
            /*  Instanciation de la connexion   */
            MySqlConnection connection = new MySqlConnection(MyConString);
            MySqlCommand command = connection.CreateCommand();
            /*  ==Ouverture de la connexion==   */
            connection.Open();
            command.CommandText = "select * from "+pNomTable+";";
            MySqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);
            pObjLsv.Items.Clear();
            while (reader.Read())
            {
                ListViewItem objitem = new ListViewItem(reader[1].ToString());
                for (int i = 2; i < reader.FieldCount; i++)
                {
                    objitem.SubItems.Add(reader[i].ToString());
                }
                pObjLsv.Items.Add(objitem);

            }



            /*    Fermeture de la connexion    */
            connection.Close();
        }
        public static void RemplirLsvMusiqueSelonID_CD(ListView pObjLsv, int pID)
        {
            /*  Instanciation de la connexion   */
            MySqlConnection connection = new MySqlConnection(MyConString);
            MySqlCommand command = connection.CreateCommand();
            /*  ==Ouverture de la connexion==   */
            connection.Open();
            command.CommandText = "select Id_Musique from CD_Musique where Id_CD=" + pID + ";";
            MySqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);
            pObjLsv.Items.Clear();
            while (reader.Read())
            {
                Music objMus = Music.GetMusicByID(int.Parse(reader[0].ToString()));
                ListViewItem objitem = new ListViewItem(objMus.Nom.ToString());
                    objitem.SubItems.Add(objMus.Longueur.ToString());
                
                pObjLsv.Items.Add(objitem);

            }



            /*    Fermeture de la connexion    */
            connection.Close();
        }

        public static List<int> IndexMusiqueSelonCD(int IdCd)
        {
            List<int> colInt = new List<int>();
            MySqlConnection connection = new MySqlConnection(MyConString);
            MySqlCommand command = connection.CreateCommand();
            /*  ==Ouverture de la connexion==   */
            connection.Open();
            command.CommandText = "select * from CD_Musique where Id_CD =" + IdCd + ";";
            MySqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);
            while (reader.Read())
            {
                try
                {
                    colInt.Add((int)reader[1]);
                }
                catch (Exception)
                {

                    MessageBox.Show("erreur");
                }
                

            }



            /*    Fermeture de la connexion    */
            connection.Close();
            return colInt;
        }

       
    }
}
